package com.chao.common.util;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.chao.common.util.exception.SysException;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.List;

public class XlsUtil {

    public static void main(String[] args) {
        JSONArray s = getXlsJson(new File("C:\\Users\\pc\\Documents\\WeChat Files\\wxid_q5khawj4k5ek22\\FileStorage\\File\\2022-08\\学生.xlsx"));
        JSONArray s1 = s.getJSONArray(0);
        JSONArray lin = s1.getJSONArray(1);
        System.out.println(s.toString());
        System.out.println(lin.getString(13));
    }

    /**
     * 表格导入解析
     */
    public static JSONArray getXlsJson(File file){
        try {
            String extString = file.getName().substring(file.getName().lastIndexOf("."));
            if(".XLS".equalsIgnoreCase(extString)){
                return getXlsJson(new HSSFWorkbook(new FileInputStream(file)));
            }else if(".XLSX".equalsIgnoreCase(extString)){
                return getXlsJson(new XSSFWorkbook(new FileInputStream(file)));
            }else{
                throw new SysException("文件格式错误,请上传xls或xlsx文件");
            }
        } catch (IOException e1) {
            throw new SysException("读取文件发生错误");
        }
    }

    /**
     * 表格导入解析
     */
    public static JSONArray getXlsJson(MultipartFile file){
        try {
            InputStream stream = file.getInputStream();
            String extString = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf("."));
            if(".XLS".equalsIgnoreCase(extString)){
                return getXlsJson(new HSSFWorkbook(stream));
            }else if(".XLSX".equalsIgnoreCase(extString)){
                return getXlsJson(new XSSFWorkbook(stream));
            }else{
                throw new SysException("文件格式错误,请上传xls或xlsx文件");
            }
        } catch (IOException e1) {
            throw new SysException("读取文件发生错误");
        }
    }

    /**
     * 获取值
     */
    public static String getValue(JSONArray j, int i){
        if(j.size() > i){
            String s = j.getString(i);
            return StrUtile.isEmpty(s)?s:s.trim();
        }
        return null;
    }

    /**
     * 表格导入解析
     */
    public static JSONArray getXlsJson(Workbook b){
        JSONArray sheetArray = new JSONArray();
        b.forEach(sheet -> {
            if (null == sheet) {
                return;
            }
            JSONArray rowArray = new JSONArray();
            DecimalFormat df = new DecimalFormat("#.######");
            int size = sheet.getRow(0).getLastCellNum();
            sheet.forEach(row -> {
                JSONArray cellArray = new JSONArray();
                row.forEach(cell ->{
                    String value;
                    try {
                        if (cell.getCellTypeEnum() == CellType.NUMERIC) { //数值
                            // type == 0或转成日期后在1970-04-27 日之前 必定是数字 (1000000000是1970-04-27的毫秒时间戳)
                            if (cell.getCellStyle().getDataFormat() == 0 || cell.getDateCellValue().getTime() < 1000000000) {
                                value = df.format(cell.getNumericCellValue());
                            } else if (cell.getCellStyle().getDataFormat() == 9) { //type=9表示是百分比
                                value = df.format(cell.getNumericCellValue() * 100) + "%";
                            } else { //所有不符合上述条件的  统一做日期处理 (表格中设置展示为 货币/会计专用/分数/特殊/自定义 格式并且数值大于25685 的单元格 获取值会报错)
                                value = DateUtile.toYmd(cell.getDateCellValue());
                            }
                        } else if(cell.getCellTypeEnum() == CellType.STRING){ //公式
                            value = cell.getRichStringCellValue().getString();
                        }else{ //其他
                            value = cell.getStringCellValue().trim();
                        }
                    }catch (Exception e){
                        value = new DataFormatter().formatCellValue(cell);
                    }
                    while(cell.getColumnIndex() > cellArray.size()){
                        cellArray.add("");
                    }
                    cellArray.add(value.trim());
                });
                boolean flag = false;
                for(Object str:cellArray){
                    if(null != str && !StrUtile.isEmpty(str.toString())){
                        flag = true;
                        break;
                    }
                }
                if(flag){
                    for (int i = cellArray.size(); i < size; i++) {
                        cellArray.add(null);
                    }
                    rowArray.add(cellArray);
                }
            });
            sheetArray.add(rowArray);
        });
        return sheetArray;
    }

    /**
     * 导出表格生成
     */
    public static HSSFWorkbook getXls(List<JSONObject> list, String[] keys){

        //声明一个工作簿
        HSSFWorkbook workbook = new HSSFWorkbook();
        try {
            //设置样式
            HSSFCellStyle style = workbook.createCellStyle();
            style.setAlignment(HorizontalAlignment.CENTER);
            style.setVerticalAlignment(VerticalAlignment.CENTER);
            style.setBorderBottom(BorderStyle.THIN); // 下边框
            style.setBorderLeft(BorderStyle.THIN);// 左边框
            style.setBorderTop(BorderStyle.THIN);// 上边框
            style.setBorderRight(BorderStyle.THIN);// 右边框

            //生成一个表格
            HSSFSheet sheet = workbook.createSheet("Sheet1");
            sheet.setDefaultColumnWidth(10);
            JSONObject hide = list.get(0);

            //设置表头
            HSSFRow row = sheet.createRow(0);
            for(int i = 0;i<keys.length;i++){
                HSSFCell cell = row.createCell(i);
                cell.setCellValue(hide.getString(keys[i]));
                cell.setCellStyle(style);
            }

            for(int i = 1;i<list.size();i++){
                row = sheet.createRow(i);
                for(int j = 0;j<keys.length;j++){
                    HSSFCell cell = row.createCell(j);
                    if(list.get(i).containsKey(keys[j])){
                        cell.setCellValue(list.get(i).getString(keys[j]));
                    }else{
                        cell.setCellValue("");
                    }
                    cell.setCellStyle(style);
                }
            }
            //workbook将Excel写入到response的输出流中，供页面下载

        } catch (Exception e) {
            e.printStackTrace();
        }
        return workbook;
    }
}